﻿Imports System.Data
Imports System.Windows.Forms

Public Class frmFilter

    Private dtFilter As New DataTable
    Public FilterString As String = ""

    'after user clicks OK, retrieve these two properties
    Public SelectedItems As List(Of String)

    Public Sub New(dt As DataTable)
        InitializeComponent()
        dtFilter = dt
    End Sub

    Private Sub btnAddFilter_Click(sender As Object, e As EventArgs) Handles btnAddFilter.Click

        Dim FilterString As String = Me.txtFilter.Text
        Dim ColName As String = Me.cboColumnName.Text

        'see if all values are numeric (when numbers are stored as strings)
        Dim IsAllNumeric As Boolean = True
        For Each r As DataRow In dtFilter.Rows
            If Not (TestNull(r.Item(ColName), "").trim = "" Or IsNumeric(r.Item(ColName))) Then IsAllNumeric = False : Exit For
        Next

        If FilterString <> "" Then FilterString &= " " & cboCondition.Text & " "
        If Me.cboFilter.Text.ToUpper.Contains("NULL") Then
            FilterString &= String.Format("{0} {1}", ColName, Me.cboFilter.Text)
        ElseIf dgFilter.Columns.Item(ColName).ValueType Is GetType(String) Then
            If IsAllNumeric Then
                FilterString &= String.Format("CONVERT({0},'System.Double') {2} {1}", ColName, Me.cboFilter.Text, IIf(IsNumeric(Me.cboFilter.Text), "=", ""))
            Else
                FilterString &= String.Format("{0} LIKE '{1}'", ColName, Me.cboFilter.Text)
            End If
        ElseIf dgFilter.Columns.Item(ColName).ValueType Is GetType(Double) Or dgFilter.Columns.Item(ColName).ValueType Is GetType(Decimal) Then
            'if is numeric or filter string contains an operator like <, etc.; otherwise assume =
            FilterString &= String.Format("{0} {2} {1}", ColName, Me.cboFilter.Text, IIf(IsNumeric(Me.cboFilter.Text), "=", ""))
        ElseIf dgFilter.Columns.Item(ColName).ValueType Is GetType(Date) Then
            'if is non-numeric, assume filter string contains an operator like <, etc.; otherwise assume =
            FilterString &= String.Format("{0} {2} {1}", ColName, Me.cboFilter.Text, IIf(IsDate(Me.cboFilter.Text), "=", ""))
        Else 'all other types use =
            FilterString &= String.Format("{0} = {1}", ColName, Me.cboFilter.Text)
        End If
        cboFilter.Text = ""
        Me.txtFilter.Text = FilterString
        ResetFilter()
    End Sub

    Private Sub btnCancel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCancel.Click
        Me.DialogResult = System.Windows.Forms.DialogResult.Cancel
        Me.Close()
    End Sub

    Private Sub btnClearFilters_Click(sender As Object, e As EventArgs) Handles btnClearFilters.Click
        Me.txtFilter.Text = ""
        ResetFilter()
    End Sub

    Private Sub btnHints_Click(sender As Object, e As EventArgs) Handles btnHints.Click
        MessageBox.Show("Text columns are filtered by selecting an exact match or by typing wildcard characters (e.g., *stream* finds 'Downstream' and 'Trout Stream Park'); the character case is ignored. For an exact match, select on the drop-down or double-click a cell in the grid.\n\nNumeric columns are filtered by exact matches or you can add comparison operators like <, >=, etc.\n\nYou can build more complex filtering criteria by editing the current filter string, perhaps adding parenthesis or changing AND to OR conditions.".Replace("\n", vbCr), "Selection Hints", MessageBoxButtons.OK)
    End Sub

    Private Sub btnOK_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnOK.Click
        SelectedItems = New List(Of String)
        Dim fieldname As String = txtFilter.Text.Split(New Char() {" "})(0)
        For Each r As DataGridViewRow In dgFilter.Rows
            If dtFilter.Columns.Contains("NAME") Then
                SelectedItems.Add(TestNull(r.Cells("NAME").Value, ""))
            ElseIf dtFilter.Columns.Contains("DESCRIPTION") Then
                SelectedItems.Add(TestNull(r.Cells("DESCRIPTION").Value, ""))
            Else
                SelectedItems.Add(TestNull(r.Cells(0).Value, ""))
            End If
        Next
        FilterString = txtFilter.Text
        Me.DialogResult = System.Windows.Forms.DialogResult.OK
        Me.Close()
    End Sub

    Private Sub cboColumnName_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles cboColumnName.SelectedIndexChanged, cboCondition.SelectedIndexChanged
        If cboColumnName.Text = "" Then Exit Sub
        cboFilter.Text = ""
        Dim lst As New List(Of String)
        For Each r As DataGridViewRow In dgFilter.Rows
            Dim s As String = TestNull(r.Cells(cboColumnName.SelectedIndex).Value, "")
            If Not lst.Contains(s) Then lst.Add(s)
        Next
        With cboFilter
            .Text = ""
            .Items.Clear()
            .Items.AddRange(lst.ToArray)
        End With
    End Sub

    Private Sub cboFilter_Enter(sender As Object, e As EventArgs) Handles cboFilter.Enter
        Me.AcceptButton = btnAddFilter
    End Sub

    Private Sub cboFilter_Leave(sender As Object, e As EventArgs) Handles cboFilter.Leave
        Me.AcceptButton = btnOK
    End Sub

    Private Sub cboFilter_Validated(ByVal sender As Object, ByVal e As System.EventArgs) Handles cboFilter.Validated
        cboFilter.Text = cboFilter.Text.Replace("'", "")
    End Sub

    Private Sub dgFilter_DoubleClick(sender As Object, e As EventArgs) Handles dgFilter.DoubleClick
        cboColumnName.SelectedIndex = dgFilter.SelectedCells(0).ColumnIndex
        cboFilter.Text = TestNull(dgFilter.SelectedCells(0).Value, "")
        btnAddFilter.PerformClick()
    End Sub

    Private Sub dgFilter_KeyDown(sender As Object, e As KeyEventArgs) Handles dgFilter.KeyDown
        If e.KeyCode = Keys.Enter Then btnOK.PerformClick()
    End Sub

    Private Sub dgFilter_SelectionChanged(sender As Object, e As EventArgs) Handles dgFilter.SelectionChanged
        If dgFilter.SelectedCells.Count > 0 AndAlso cboColumnName.Items.Count > 0 Then cboColumnName.SelectedIndex = dgFilter.SelectedCells(0).ColumnIndex
    End Sub

    Private Sub frmFilter_Load(sender As Object, e As EventArgs) Handles Me.Load
        dgFilter.DataSource = dtFilter
        txtFilter.Text = FilterString
        ResetFilter()
        With dgFilter
            Me.cboColumnName.Items.Clear()
            For Each col As DataGridViewColumn In .Columns
                If col.Visible Then cboColumnName.Items.Add(col.Name)
            Next
        End With
        If Me.cboColumnName.Items.Count > 0 Then Me.cboColumnName.SelectedIndex = 0
    End Sub

    Private Sub ResetFilter()
        Dim bs As New BindingSource(dgFilter.DataSource, dgFilter.DataMember)
        Dim FilterStringOld As String = FilterString
        Try
            Dim FilterStr As String = Me.txtFilter.Text
            If FilterStr = "" Then
                bs.RemoveFilter()
                cboCondition.Visible = False
            Else
                bs.RemoveFilter()
                bs.Filter = FilterStr
                cboCondition.Visible = True
                cboCondition.SelectedIndex = 0
            End If
            dgFilter.DataSource = bs
            FilterString = FilterStr

        Catch ex As Exception
            FilterString = FilterStringOld
            bs.Filter = FilterString
        End Try
    End Sub

    Private Sub txtFilter_TextChanged(sender As Object, e As EventArgs) Handles txtFilter.TextChanged
        ResetFilter()
    End Sub

End Class
